package com.fage.main;

import com.fage.pojo.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcTemplateDemo {
    public static void main(String[] args) {
        //1.获取Spring容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2.根据id获取bean对象
        JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
        //插入
        jt.execute("insert into account(name,money)values('eee',500)");
        //保存
        jt.update("insert into account(name,money)values(?,?)", "fff", 5000);
        //修改
        jt.update("update account set money = money-? where id = ?", 300, 6);
        //删除
        jt.update("delete from account where id = ?", 6);
        //查询所有
        List<Account> accounts = jt.query("select * from account where money > ? ", new AccountRowMapper(), 500);
        for (Account o : accounts) {
            System.out.println(o);
        }
        //使用 RowMapper 的方式查询一个（常用的方式）
        List<Account> as = jt.query("select * from account where id = ? ", new AccountRowMapper(), 55);
        System.out.println(as.isEmpty() ? "没有结果" : as.get(0));
        //使用 ResultSetExtractor 的方式查询一个（不常用的方式）
        Account account = jt.query("select * from account where id = ?", new AccountResultSetExtractor(), 3);
        System.out.println(account);
        //查询返回一行一列：使用聚合函数，在不使用 group by 字句时，都是返回一行一列。最常用的就是分页中获取总记录条数
        Integer total = jt.queryForObject("select count(*) from account where money > ?", Integer.class, 500);
        System.out.println(total);
    }
}

/**
 * 定义Account的封装策略
 */
class AccountRowMapper implements RowMapper<Account> {
    /**
     * 把结果集中的数据封装到Account中，然后由spring把每个Account加到集合中
     *
     * @param rs
     * @param rowNum
     * @return
     * @throws SQLException
     */
    @Override
    public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
        Account account = new Account();
        account.setId(rs.getInt("id"));
        account.setName(rs.getString("name"));
        account.setMoney(rs.getFloat("money"));
        return account;
    }
}

class AccountResultSetExtractor implements ResultSetExtractor<Account> {

    @Override
    public Account extractData(ResultSet rs) throws SQLException, DataAccessException {
        Account account = new Account();
        account.setId(rs.getInt("id"));
        account.setName(rs.getString("name"));
        account.setMoney(rs.getFloat("money"));
        return account;
    }
}